package cn.myschool.java79.dao;

import cn.myschool.java79.entity.Subject;
import cn.myschool.java79.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author oldliu
 * @since 1.0
 */
//返回结果
public class SubjectDao {
    //至少是5个方法
    public int add(Subject sub){

        return 0;
    }
    public int update(Subject sub){
        return 0;
    }
    public int del(long id){
        return 0;
    }
    public Subject get(long id){
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        try {
            con = DBUtil.getConnection();
            ps = con.prepareStatement("select * from s_subject where id=?");
            ps.setLong(1,id);
            rs=ps.executeQuery();
            if(rs.next()){
                Subject sub=new Subject();
                sub.setId(rs.getLong("id"));
                sub.setClasshour(rs.getInt("classhour"));
                sub.setGradeid(rs.getLong("gradeid"));
                sub.setSubjectname(rs.getString("subjectname"));
                return sub;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(rs,ps,con);
        }
        return null;
    }

    /**
     * 分页查找
     * @param pageNo  页号
     * @param pageSize 页大小
     * @return
     */
    public List<Subject> search(int pageNo,int pageSize){
        String sql="select s.*,g.gname from s_subject s inner join s_grade g on s.gradeid=g.id order by s.id asc limit ?,?";
        List<Subject> data=new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        try {
            con = DBUtil.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1,(pageNo-1)*pageSize);
            ps.setInt(2,pageSize);
            rs=ps.executeQuery();
            while (rs.next()){
                Subject sub=new Subject();
                sub.setId(rs.getLong("id"));
                sub.setClasshour(rs.getInt("classhour"));
                sub.setGradeid(rs.getLong("gradeid"));
                sub.setSubjectname(rs.getString("subjectname"));
                sub.setGradename(rs.getString("gname"));
                data.add(sub);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(rs,ps,con);
        }
        return  data;
    }
}
